package org.zjvis.datascience.common.sql;

import org.apache.commons.lang3.StringUtils;
import org.zjvis.datascience.common.widget.config.WidgetConfig;
import org.zjvis.datascience.common.widget.config.WidgetGroupConfig;
import org.zjvis.datascience.common.widget.config.WidgetSelectedConfig;
import org.zjvis.datascience.common.widget.config.WidgetWhereConfig;

import java.util.List;

/**
 * @description : Greenplum SQL拼装帮助类
 * @date 2021-12-01
 */
public class GPSqlHelper {
    private static final String SQL_TEMPLATE = "SELECT %s \nFROM %s \n%s \n%s \n%s \n%s";

    public static String assembleSql(String tableName, WidgetConfig config) {
        String selected = assembleSelectSql(config.getSelected());
        String whereStr = assembleWhereSql(config.getWhere());
        String groupBy = assembleGroupBySql(config.getGroup());
        String orderBy = "";
        String limitStr = "";
        if (config.getTop() != null) {
            limitStr = " limit " + config.getTop();
        }

        return String.format(SQL_TEMPLATE, selected, tableName, whereStr, groupBy, orderBy, limitStr);
    }

    private static String columnFormat(String col) {
        if (StringUtils.isNotBlank(col)) {
            return "\"" + col + "\"";
        }
        return "";
    }

    private static String assembleOrderSql() {
        return "";
    }

    private static String assembleSelectSql(List<WidgetSelectedConfig> selectList) {
        StringBuilder sql = new StringBuilder();

        if (!selectList.isEmpty()) {
            int j = 0;
            for (WidgetSelectedConfig select : selectList) {
                String name = columnFormat(select.getName());
                String alias = columnFormat(select.getAlias());
                String aggType = select.getAggType();
                if (StringUtils.isNotBlank(aggType)) {
                    switch (aggType) {
                        case "sum":
                            name = "sum(" + name + ") " + alias;
                            break;
                        case "avg":
                            name = "avg(" + name + ") " + alias;
                            break;
                        default:
                            break;
                    }
                }

                if (j == 0) {
                    sql.append(name);
                } else {
                    sql.append(",").append(name);
                }
                j++;
            }
        }
        return sql.toString();
    }

    private static String assembleWhereSql(List<WidgetWhereConfig> whereList) {
        StringBuilder sql = new StringBuilder();
        int k = 0;
        sql.append(" where ");
        for (WidgetWhereConfig where : whereList) {
            String name = columnFormat(where.getName());
            String type = where.getFilterType();
            List<String> values = where.getValues();

            if (StringUtils.isNotBlank(type) && values != null && !values.isEmpty()) {
                switch (type) {
                    case "(a,b)":
                        name = "(" + name + ">" + values.get(0) + " AND " + name + "<" + values.get(1) + ")";
                        break;
                    case "(a,b]":
                        name = "(" + name + ">" + values.get(0) + " AND " + name + "<=" + values.get(1) + ")";
                        break;
                    case "[a,b)":
                        name = "(" + name + ">=" + values.get(0) + " AND " + name + "<" + values.get(1) + ")";
                        break;
                    case "[a,b]":
                        name = "(" + name + ">=" + values.get(0) + " AND " + name + "<=" + values.get(1) + ")";
                        break;
                    default:
                        name = name + type + values.get(0);
                        break;
                }
            } else {
                continue;
            }
            if (k == 0) {
                sql.append(name);
            } else {
                sql.append(" and ").append(name);
            }
            k++;
        }
        return sql.toString();
    }

    private static String assembleGroupBySql(List<WidgetGroupConfig> groupList) {
        StringBuilder sql = new StringBuilder();
        int l = 0;
        sql.append(" group by ");
        for (WidgetGroupConfig group : groupList) {
            String name = columnFormat(group.getName());
            if (l == 0) {
                sql.append(name);
            } else {
                sql.append(",").append(name);
            }
            l++;
        }
        return sql.toString();
    }
}
